﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Maticsoft.DBUtility;
using System.Collections.Generic;//Please add references
namespace EnterpriseFund.DAL
{
    /// <summary>
    /// 数据访问类:AllowanceApplicationForm
    /// </summary>
    public partial class AllowanceApplicationForm
    {
        public AllowanceApplicationForm()
        { }
        #region  BasicMethod

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string FApplicationFormID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from fund.t_AllowanceApplicationForm");
            strSql.Append(" where FApplicationFormID=@FApplicationFormID ");
            SqlParameter[] parameters = {
					new SqlParameter("@FApplicationFormID", SqlDbType.NVarChar,50)			};
            parameters[0].Value = FApplicationFormID;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool UpdateFStatus(EnterpriseFund.Model.AllowanceApplicationForm model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update fund.t_AllowanceApplicationForm set ");
            strSql.Append("FStatus=@FStatus,");
            strSql.Append("FIsReturn=@FIsReturn,");
            strSql.Append("FUpdEmplNo=@FUpdEmplNo,");
            strSql.Append("FUpdDeptNo=@FUpdDeptNo,");
            strSql.Append("FUpdDate=@FUpdDate,");
            strSql.Append("FUpdIP=@FUpdIP");
            strSql.Append(" where FApplicationFormID=@FApplicationFormID ");
            SqlParameter[] parameters = {
					new SqlParameter("@FStatus", SqlDbType.NVarChar,20),
					new SqlParameter("@FIsReturn", SqlDbType.NVarChar,1),
					new SqlParameter("@FUpdEmplNo", SqlDbType.NVarChar,15),
					new SqlParameter("@FUpdDeptNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FUpdDate", SqlDbType.DateTime),
					new SqlParameter("@FUpdIP", SqlDbType.VarChar,15),
					new SqlParameter("@FApplicationFormID", SqlDbType.NVarChar,50)};
            parameters[0].Value = model.FStatus;
            parameters[1].Value = model.FIsReturn;
            parameters[2].Value = model.FUpdEmplNo;
            parameters[3].Value = model.FUpdDeptNo;
            parameters[4].Value = model.FUpdDate;
            parameters[5].Value = model.FUpdIP;
            parameters[6].Value = model.FApplicationFormID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool UpdateFStatus2(EnterpriseFund.Model.AllowanceApplicationForm model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update fund.t_AllowanceApplicationForm set ");
            strSql.Append("FStatus=@FStatus,");
            strSql.Append("FApplyName=@FApplyName,");
            strSql.Append("FIsReturn=@FIsReturn");
            strSql.Append(" where FApplicationFormID=@FApplicationFormID ");
            SqlParameter[] parameters = {
                    new SqlParameter("@FStatus", SqlDbType.NVarChar,20),
					new SqlParameter("@FApplyName", SqlDbType.NVarChar,20),
					new SqlParameter("@FIsReturn", SqlDbType.NVarChar,1),
					new SqlParameter("@FApplicationFormID", SqlDbType.NVarChar,50)};
            parameters[0].Value = model.FStatus;
            parameters[1].Value = model.FApplyName;
            parameters[2].Value = model.FIsReturn;
            parameters[3].Value = model.FApplicationFormID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public void UpdateFStatus3(EnterpriseFund.Model.AllowanceApplicationForm model)
        {
            List<CommandInfo> cmdList = new List<CommandInfo>();
            CommandInfo cmds = new CommandInfo();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update fund.t_AllowanceApplicationForm set ");
            strSql.Append("FStatus=@FStatus,");
           // strSql.Append("FApplyName=@FApplyName,");
            strSql.Append("FIsReturn=@FIsReturn");
            strSql.Append(" where FApplicationFormID=@FApplicationFormID ");
            SqlParameter[] parameters = {
                    new SqlParameter("@FStatus", SqlDbType.NVarChar,20),
				//	new SqlParameter("@FApplyName", SqlDbType.NVarChar,20),
					new SqlParameter("@FIsReturn", SqlDbType.NVarChar,1),
					new SqlParameter("@FApplicationFormID", SqlDbType.NVarChar,50)};
            parameters[0].Value = model.FStatus;
         //   parameters[1].Value = model.FApplyName;
            parameters[1].Value = model.FIsReturn;
            parameters[2].Value = model.FApplicationFormID;

            // int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters;
            cmdList.Add(cmds);
            if (cmdList.Count > 0)
            {
               
                    DbHelperSQL.ExecuteSqlTranWithString(cmdList);
               
            }
          
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public string Add(EnterpriseFund.Model.AllowanceApplicationForm model)
        {
            StringBuilder strSql = new StringBuilder();
            List<CommandInfo> cmdList = new List<CommandInfo>();
            CommandInfo cmds = new CommandInfo();

            var getBillIdSql = "exec sp_getbillno 'fund.t_AllowanceApplicationForm','FApplicationFormID','','','',@returnBId output ";
            var getBillIdPar = new SqlParameter[]{ 
                new SqlParameter("@returnBId", SqlDbType.NVarChar, 50)
            };

            getBillIdPar[0].Direction = ParameterDirection.Output;

            cmds.CommandText = getBillIdSql.ToString();
            cmds.Parameters = getBillIdPar;
            cmdList.Add(cmds);

            cmds = new CommandInfo();
            strSql = new StringBuilder();
            strSql.Append("insert into fund.t_AllowanceApplicationForm(");
            strSql.Append("FApplicationFormID,FSource,FSourceNo,FStatus,NSRDZDAH,NSRMC,YHZK,KHYH,FFileNo,FNote,FIsReturn,FFinancePropNo,FYear,FApplyAmount,FAuditAmount,FApplyName,FApplyDate,FEntityNo,FInsEmpNo,FInsDeptNo,FInsDate,FInsIP)");
            strSql.Append(" values (");
            strSql.Append("@returnBId,@FSource,@FSourceNo,@FStatus,@NSRDZDAH,@NSRMC,@YHZK,@KHYH,@FFileNo,@FNote,@FIsReturn,@FFinancePropNo,@FYear,@FApplyAmount,@FAuditAmount,@FApplyName,@FApplyDate,@FEntityNo,@FInsEmpNo,@FInsDeptNo,@FInsDate,@FInsIP)");
            SqlParameter[] parameters = {
					new SqlParameter("@returnBId", SqlDbType.NVarChar,50),
					new SqlParameter("@FSource", SqlDbType.NVarChar,30),
					new SqlParameter("@FSourceNo", SqlDbType.NVarChar,50),
					new SqlParameter("@FStatus", SqlDbType.NVarChar,20),
					new SqlParameter("@NSRDZDAH", SqlDbType.Decimal,13),
					new SqlParameter("@NSRMC", SqlDbType.NVarChar,80),
					new SqlParameter("@YHZK", SqlDbType.NVarChar,50),
					new SqlParameter("@KHYH", SqlDbType.NVarChar,80),
					new SqlParameter("@FFileNo", SqlDbType.NVarChar,255),
					new SqlParameter("@FNote", SqlDbType.NVarChar,255),
					new SqlParameter("@FIsReturn", SqlDbType.NVarChar,1),
					new SqlParameter("@FFinancePropNo", SqlDbType.NVarChar,6),
					new SqlParameter("@FYear", SqlDbType.Decimal,5),
					new SqlParameter("@FApplyAmount", SqlDbType.Decimal,9),
					new SqlParameter("@FAuditAmount", SqlDbType.Decimal,9),
					new SqlParameter("@FApplyName", SqlDbType.NVarChar,20),
					new SqlParameter("@FApplyDate", SqlDbType.DateTime),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FInsEmpNo", SqlDbType.NVarChar,15),
					new SqlParameter("@FInsDeptNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FInsDate", SqlDbType.DateTime),
					new SqlParameter("@FInsIP", SqlDbType.NVarChar,15)};
            parameters[0].Direction = ParameterDirection.InputOutput;
            parameters[1].Value = model.FSource;
            parameters[2].Value = model.FSourceNo;
            parameters[3].Value = model.FStatus;
            parameters[4].Value = model.NSRDZDAH;
            parameters[5].Value = model.NSRMC;
            parameters[6].Value = model.YHZK;
            parameters[7].Value = model.KHYH;
            parameters[8].Value = model.FFileNo;
            parameters[9].Value = model.FNote;
            parameters[10].Value = model.FIsReturn;
            parameters[11].Value = model.FFinancePropNo;
            parameters[12].Value = model.FYear;
            parameters[13].Value = model.FApplyAmount;
            parameters[14].Value = model.FAuditAmount;
            parameters[15].Value = model.FApplyName;
            parameters[16].Value = model.FApplyDate;
            parameters[17].Value = model.FEntityNo;
            parameters[18].Value = model.FInsEmpNo;
            parameters[19].Value = model.FInsDeptNo;
            parameters[20].Value = model.FInsDate;
            parameters[21].Value = model.FInsIP;

            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters;
            cmdList.Add(cmds);

            if (cmdList.Count > 0)
            {
                DbHelperSQL.ExecuteSqlTranWithString(cmdList);
            }

            return cmdList[0].Parameters[0].Value.ToString();
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(EnterpriseFund.Model.AllowanceApplicationForm model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update fund.t_AllowanceApplicationForm set ");
            strSql.Append("FSource=@FSource,");
            strSql.Append("FSourceNo=@FSourceNo,");
            strSql.Append("FStatus=@FStatus,");
            strSql.Append("NSRMC=@NSRMC,");
            strSql.Append("YHZK=@YHZK,");
            strSql.Append("KHYH=@KHYH,");
            strSql.Append("FFileNo=@FFileNo,");
            strSql.Append("FNote=@FNote,");
            strSql.Append("FIsReturn=@FIsReturn,");
            strSql.Append("FApplyAmount=@FApplyAmount,");
            strSql.Append("FAuditAmount=@FAuditAmount,");
            strSql.Append("FApplyName=@FApplyName,");
            strSql.Append("FApplyDate=@FApplyDate,");
            strSql.Append("FInsEmpNo=@FInsEmpNo,");
            strSql.Append("FInsDeptNo=@FInsDeptNo,");
            strSql.Append("FInsDate=@FInsDate,");
            strSql.Append("FInsIP=@FInsIP,");
            strSql.Append("FUpdEmplNo=@FUpdEmplNo,");
            strSql.Append("FUpdDeptNo=@FUpdDeptNo,");
            strSql.Append("FUpdDate=@FUpdDate,");
            strSql.Append("FUpdIP=@FUpdIP");
            strSql.Append(" where FApplicationFormID=@FApplicationFormID ");
            SqlParameter[] parameters = {
					new SqlParameter("@FSource", SqlDbType.NVarChar,30),
					new SqlParameter("@FSourceNo", SqlDbType.NVarChar,50),
					new SqlParameter("@FStatus", SqlDbType.NVarChar,20),
					new SqlParameter("@NSRMC", SqlDbType.NVarChar,80),
					new SqlParameter("@YHZK", SqlDbType.NVarChar,50),
					new SqlParameter("@KHYH", SqlDbType.NVarChar,80),
					new SqlParameter("@FFileNo", SqlDbType.NVarChar,255),
					new SqlParameter("@FNote", SqlDbType.NVarChar,255),
					new SqlParameter("@FIsReturn", SqlDbType.NVarChar,1),
					new SqlParameter("@FApplyAmount", SqlDbType.Decimal,9),
					new SqlParameter("@FAuditAmount", SqlDbType.Decimal,9),
					new SqlParameter("@FApplyName", SqlDbType.NVarChar,20),
					new SqlParameter("@FApplyDate", SqlDbType.DateTime),
					new SqlParameter("@FInsEmpNo", SqlDbType.NVarChar,15),
					new SqlParameter("@FInsDeptNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FInsDate", SqlDbType.DateTime),
					new SqlParameter("@FInsIP", SqlDbType.NVarChar,15),
					new SqlParameter("@FUpdEmplNo", SqlDbType.NVarChar,15),
					new SqlParameter("@FUpdDeptNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FUpdDate", SqlDbType.DateTime),
					new SqlParameter("@FUpdIP", SqlDbType.VarChar,15),
					new SqlParameter("@FApplicationFormID", SqlDbType.NVarChar,50),
					new SqlParameter("@NSRDZDAH", SqlDbType.Decimal,13),
					new SqlParameter("@FFinancePropNo", SqlDbType.NVarChar,6),
					new SqlParameter("@FYear", SqlDbType.Decimal,5),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12)};
            parameters[0].Value = model.FSource;
            parameters[1].Value = model.FSourceNo;
            parameters[2].Value = model.FStatus;
            parameters[3].Value = model.NSRMC;
            parameters[4].Value = model.YHZK;
            parameters[5].Value = model.KHYH;
            parameters[6].Value = model.FFileNo;
            parameters[7].Value = model.FNote;
            parameters[8].Value = model.FIsReturn;
            parameters[9].Value = model.FApplyAmount;
            parameters[10].Value = model.FAuditAmount;
            parameters[11].Value = model.FApplyName;
            parameters[12].Value = model.FApplyDate;
            parameters[13].Value = model.FInsEmpNo;
            parameters[14].Value = model.FInsDeptNo;
            parameters[15].Value = model.FInsDate;
            parameters[16].Value = model.FInsIP;
            parameters[17].Value = model.FUpdEmplNo;
            parameters[18].Value = model.FUpdDeptNo;
            parameters[19].Value = model.FUpdDate;
            parameters[20].Value = model.FUpdIP;
            parameters[21].Value = model.FApplicationFormID;
            parameters[22].Value = model.NSRDZDAH;
            parameters[23].Value = model.FFinancePropNo;
            parameters[24].Value = model.FYear;
            parameters[25].Value = model.FEntityNo;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(string FApplicationFormID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from fund.t_AllowanceApplicationForm ");
            strSql.Append(" where FApplicationFormID=@FApplicationFormID ");
            SqlParameter[] parameters = {
					new SqlParameter("@FApplicationFormID", SqlDbType.NVarChar,50)			};
            parameters[0].Value = FApplicationFormID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string FApplicationFormIDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from fund.t_AllowanceApplicationForm ");
            strSql.Append(" where FApplicationFormID in (" + FApplicationFormIDlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public EnterpriseFund.Model.AllowanceApplicationForm GetModel(string FApplicationFormID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select  top 1 FApplicationFormID,FSource,FSourceNo,FStatus,a.NSRDZDAH,a.NSRMC,YHZK,
KHYH,FFileNo,FNote,FIsReturn,a.FFinancePropNo,FYear,FApplyAmount,FAuditAmount,FApplyName,
FApplyDate,a.FEntityNo,FInsEmpNo,FInsDeptNo,FInsDate,FInsIP,FUpdEmplNo,FUpdDeptNo,
FUpdDate,FUpdIP,b.FDDBRMC,c.FFinancePropName,d.FEmpName AS FInsEmpName,e.FEmpName AS FUpdEmplName from fund.t_AllowanceApplicationForm a
LEFT JOIN fund.V_DJ_NSRXX b ON  a.NSRDZDAH = b.NSRDZDAH 
LEFT JOIN fund.t_FinanceProp c ON a.FFinancePropNo=c.FFinancePropNo
LEFT JOIN dbo.v_PrvUser d ON a.FInsEmpNo=d.FEmplNo
LEFT JOIN dbo.v_PrvUser e ON a.FUpdEmplNo=e.FEmplNo ");
            strSql.Append(" where FApplicationFormID=@FApplicationFormID ");
            SqlParameter[] parameters = {
					new SqlParameter("@FApplicationFormID", SqlDbType.NVarChar,50)			};
            parameters[0].Value = FApplicationFormID;

            EnterpriseFund.Model.AllowanceApplicationForm model = new EnterpriseFund.Model.AllowanceApplicationForm();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public EnterpriseFund.Model.AllowanceApplicationForm GetModel1(string FApplicationFormID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select * ,Famount,FAuditAmount-Famount as FamountLeave from fund.t_allowanceApplicationForm left join 
(select SUM(FAmount) as Famount,FApplicationFormID as FApplicationFormID1 from fund.t_AllowancePayEntry group by FApplicationFormID ) as t 
on  fund.t_allowanceApplicationForm.FApplicationFormID=t.FApplicationFormID1  
where FStatus=3 and FIsReturn='N' and (FAuditAmount-famount>0 or Famount is null) and fund.t_allowanceApplicationForm.FApplicationFormID=@FApplicationFormID");
            SqlParameter[] parameters = {
					new SqlParameter("@FApplicationFormID", SqlDbType.NVarChar,50)			};
            parameters[0].Value = FApplicationFormID;
            EnterpriseFund.Model.AllowanceApplicationForm model = new EnterpriseFund.Model.AllowanceApplicationForm();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public EnterpriseFund.Model.AllowanceApplicationForm DataRowToModel(DataRow row)
        {
            EnterpriseFund.Model.AllowanceApplicationForm model = new EnterpriseFund.Model.AllowanceApplicationForm();
            if (row != null)
            {
                if (row["FApplicationFormID"] != null)
                {
                    model.FApplicationFormID = row["FApplicationFormID"].ToString();
                }
                if (row["FSource"] != null)
                {
                    model.FSource = row["FSource"].ToString();
                }
                if (row["FSourceNo"] != null)
                {
                    model.FSourceNo = row["FSourceNo"].ToString();
                }
                if (row["FStatus"] != null)
                {
                    model.FStatus = row["FStatus"].ToString();
                }
                if (row["NSRDZDAH"] != null && row["NSRDZDAH"].ToString() != "")
                {
                    model.NSRDZDAH = decimal.Parse(row["NSRDZDAH"].ToString());
                }
                if (row["NSRMC"] != null)
                {
                    model.NSRMC = row["NSRMC"].ToString();
                }
                if (row["YHZK"] != null)
                {
                    model.YHZK = row["YHZK"].ToString();
                }
                if (row["KHYH"] != null)
                {
                    model.KHYH = row["KHYH"].ToString();
                }
                if (row["FFileNo"] != null)
                {
                    model.FFileNo = row["FFileNo"].ToString();
                }
                if (row["FNote"] != null)
                {
                    model.FNote = row["FNote"].ToString();
                }
                if (row["FIsReturn"] != null)
                {
                    model.FIsReturn = row["FIsReturn"].ToString();
                }
                if (row["FFinancePropNo"] != null)
                {
                    model.FFinancePropNo = row["FFinancePropNo"].ToString();
                }
                if (row["FYear"] != null && row["FYear"].ToString() != "")
                {
                    model.FYear = decimal.Parse(row["FYear"].ToString());
                }
                if (row["FApplyAmount"] != null && row["FApplyAmount"].ToString() != "")
                {
                    model.FApplyAmount = decimal.Parse(row["FApplyAmount"].ToString());
                }
                if (row["FAuditAmount"] != null && row["FAuditAmount"].ToString() != "")
                {
                    model.FAuditAmount = decimal.Parse(row["FAuditAmount"].ToString());
                }
                if (row["FApplyName"] != null)
                {
                    model.FApplyName = row["FApplyName"].ToString();
                }
                if (row["FApplyDate"] != null && row["FApplyDate"].ToString() != "")
                {
                    model.FApplyDate = DateTime.Parse(row["FApplyDate"].ToString());
                }
                if (row["FEntityNo"] != null)
                {
                    model.FEntityNo = row["FEntityNo"].ToString();
                }
                if (row["FInsEmpNo"] != null)
                {
                    model.FInsEmpNo = row["FInsEmpNo"].ToString();
                }
                if (row["FInsDeptNo"] != null)
                {
                    model.FInsDeptNo = row["FInsDeptNo"].ToString();
                }
                if (row["FInsDate"] != null && row["FInsDate"].ToString() != "")
                {
                    model.FInsDate = DateTime.Parse(row["FInsDate"].ToString());
                }
                if (row["FInsIP"] != null)
                {
                    model.FInsIP = row["FInsIP"].ToString();
                }
                if (row["FUpdEmplNo"] != null)
                {
                    model.FUpdEmplNo = row["FUpdEmplNo"].ToString();
                }
                if (row["FUpdDeptNo"] != null)
                {
                    model.FUpdDeptNo = row["FUpdDeptNo"].ToString();
                }
                if (row["FUpdDate"] != null && row["FUpdDate"].ToString() != "")
                {
                    model.FUpdDate = DateTime.Parse(row["FUpdDate"].ToString());
                }
                if (row["FUpdIP"] != null)
                {
                    model.FUpdIP = row["FUpdIP"].ToString();
                }
                if (row.Table.Columns.Contains("FDDBRMC"))
                {
                    if (row["FDDBRMC"] != null && row["FDDBRMC"].ToString() != "")
                    {
                        model.FDDBRMC = row["FDDBRMC"].ToString();
                    }
                }
                if (row.Table.Columns.Contains("FFinancePropName"))
                {
                    if (row["FFinancePropName"] != null)
                    {
                        model.FFinancePropName = row["FFinancePropName"].ToString();
                    }
                }
                if (row.Table.Columns.Contains("FInsEmpName"))
                {
                    if (row["FInsEmpName"] != null && row["FInsEmpName"].ToString() != "")
                    {
                        model.FInsEmpName = row["FInsEmpName"].ToString();
                    }
                }
                if (row.Table.Columns.Contains("FUpdEmplName"))
                {
                    if (row["FUpdEmplName"] != null)
                    {
                        model.FUpdEmplName = row["FUpdEmplName"].ToString();
                    }
                }
                if (row.Table.Columns.Contains("Famount"))
                {
                    if (row["Famount"] != null)
                    {
                        model.Famount = row["Famount"].ToString();
                    }
                }
                if (row.Table.Columns.Contains("FamountLeave"))
                {
                    if (row["FamountLeave"] != null)
                    {
                        model.FamountLeave = row["FamountLeave"].ToString();
                    }
                }
            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FApplicationFormID,FSource,FSourceNo,FStatus,NSRDZDAH,NSRMC,YHZK,KHYH,FFileNo,FNote,FIsReturn,FFinancePropNo,FYear,FApplyAmount,FAuditAmount,FApplyName,FApplyDate,FEntityNo,FInsEmpNo,FInsDeptNo,FInsDate,FInsIP,FUpdEmplNo,FUpdDeptNo,FUpdDate,FUpdIP ");
            strSql.Append(" FROM fund.t_AllowanceApplicationForm ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        public DataSet GetList1(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select * ,Famount,FAuditAmount-Famount as FamountLeave from fund.t_allowanceApplicationForm left join 
(select SUM(FAmount) as Famount,FApplicationFormID as FApplicationFormID1 from fund.t_AllowancePayEntry group by FApplicationFormID ) as t 
on  fund.t_allowanceApplicationForm.FApplicationFormID=t.FApplicationFormID1  
where FStatus=3 and FIsReturn='N' and (FAuditAmount-famount>0 or Famount is null)");
            strSql.Append(strWhere);
            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataSet GetListAllowanceApplicationFormFront(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select * from ( SELECT c.*,d.NSRMC,d.NSRBM,isnull(e.FApplicationFormID,-1) as FApplicationFormID,f.FFinancePropShortName,
                    ISNULL(e.FStatus,-1) AS FStatus FROM (
SELECT a.FAmount,a.NSRDZDAH,b.FFileNo,a.FAllowanceFileID,'A' AS FSource,b.FFinancePropNo
FROM fund.t_AllowanceFileEntry a 
LEFT JOIN fund.t_AllowanceFile b ON a.FAllowanceFileID = b.FAllowanceFileID 
WHERE  FFileNo is not null
UNION ALL
SELECT a.FUpLevelAmount,a.NSRDZDAH,b.FFileNo,a.FUpLevelAllowanceID,'U' AS FSource,b.FFinancePropNo FROM fund.t_UpLevelAllowanceEntry a 
LEFT JOIN fund.t_UpLevelAllowance b ON a.FUpLevelAllowanceID = b.FUpLevelAllowanceID
WHERE FStatus ='A') c LEFT JOIN ltais.DJ_NSRXX d ON c.NSRDZDAH = d.NSRDZDAH
LEFT JOIN fund.t_AllowanceApplicationForm e ON c.FAllowanceFileID=e.FSourceNo AND c.FSource=e.FSource AND e.NSRDZDAH=c.NSRDZDAH
LEFT JOIN fund.t_FinanceProp f ON c.FFinancePropNo = f.FFinancePropNo) as a
");
            if (strWhere != "")
            {
                strSql.Append(@" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" FApplicationFormID,FSource,FSourceNo,FStatus,NSRDZDAH,NSRMC,YHZK,KHYH,FFileNo,FNote,FIsReturn,FFinancePropNo,FYear,FApplyAmount,FAuditAmount,FApplyName,FApplyDate,FEntityNo,FInsEmpNo,FInsDeptNo,FInsDate,FInsIP,FUpdEmplNo,FUpdDeptNo,FUpdDate,FUpdIP ");
            strSql.Append(" FROM fund.t_AllowanceApplicationForm ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM fund.t_AllowanceApplicationForm ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        public int GetRecordCountv(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM fund.v_AllowancePay ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.FApplicationFormID desc");
            }
            strSql.Append(")AS Row, T.*  from fund.t_AllowanceApplicationForm T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@IsReCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                    };
            parameters[0].Value = "t_AllowanceApplicationForm";
            parameters[1].Value = "FApplicationFormID";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/

        #endregion  BasicMethod
        #region  ExtensionMethod

        #endregion  ExtensionMethod
    }
}

